package com.powerunion.datacollection.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;

import com.powerunion.datacollection.common.Constants;
import com.powerunion.datacollection.common.Format;
import com.powerunion.datacollection.dao.DaoException;
import com.powerunion.datacollection.dao.DataCollectViewDao;
import com.powerunion.datacollection.model.ActivityInstance;
import com.powerunion.datacollection.model.ApproveWorkItem;
import com.powerunion.datacollection.model.GlobalTargetsData;
import com.powerunion.datacollection.model.SubmissionWorkItem;
import com.powerunion.datacollection.model.TargetsCategory;
import com.powerunion.datacollection.model.jsonObject.GlobalTargetsDataListVO;
import com.powerunion.datacollection.model.jsonObject.GlobalTargetsDataVO;
import com.powerunion.datacollection.model.jsonObject.GroupHeader;
import com.powerunion.datacollection.persistence.db.jdbc.operator.QueryOperator;
import com.powerunion.datacollection.persistence.db.jdbc.operator.RsDataSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameter;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlParameterSet;
import com.powerunion.datacollection.persistence.db.jdbc.operator.SqlType;
import com.powerunion.datacollection.persistence.db.jdbc.operator.UpdateOperator;
import com.powerunion.datacollection.service.ActivityManager;
import com.powerunion.datacollection.service.ApproveWorkItemManager;
import com.powerunion.datacollection.service.SubmissionWorkItemManager;
import com.powerunion.datacollection.service.TargetManager;
import com.powerunion.util.DBUtils;
import com.powerunion.util.log.LogHelper;

public class DataCollectViewDaoImpl extends JdbcBase implements
		DataCollectViewDao {
	// 初始化日志类
	private static final Logger logger = LogHelper.getInstance().getLogger(
			DataCollectViewDaoImpl.class);

	public List getTargetCategorys(String formId) {
		List targetCategorys = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"select distinct targets_category_id,c.category_name from dc_global_data g ")
				.append(
						" left join DC_item_CATEGORY c on g.targets_category_id = c.id ")
				.append("where document_id=? and i_template = 0");
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, formId);
			rs = ps.executeQuery();
			while (rs.next()) {
				TargetsCategory tc = new TargetsCategory();
				tc.setId(rs.getString("targets_category_id"));
				tc.setCategoryName(rs.getString("category_name"));
				targetCategorys.add(tc);
			}
		} catch (Exception e) {
			logger.error("", e);
			return targetCategorys;
		} finally {
			DBUtils.close(rs, ps, conn);
		}

		return targetCategorys;
	}

	public List getTargetDataCollectByAct(List submissionWorks)
			throws DaoException {

		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"select g.target_id,t.target_name,g.kernel_value,g.num_ext01,g.num_ext02,g.num_ext03, ")
				.append(
						"g.num_ext04,g.num_ext05,g.num_ext06,g.num_ext07,g.num_ext08,g.num_ext09,g.num_ext10,")
				.append(
						"g.char_ext01,g.char_ext02,g.char_ext03,g.char_ext04,g.char_ext05,g.document_id,g.submi_user,g.sub_date ")
				.append(
						"from dc_global_data g left join dc_target t on g.target_id=t.id ")
				.append("  where g.document_id=? and g.i_template = 1 ")
				.append(
						"  and g.targets_category_id=? and g.submi_user=? and g.proc_id=? and g.act_id=? ");
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List templateData = null;
		try {
			templateData = new ArrayList();
			conn = this.getConnection();
			List targetCategorys = new ArrayList();
			SubmissionWorkItem submissionWorkItem = (SubmissionWorkItem) submissionWorks
					.get(0);
			targetCategorys = this.getTargetCategorys(submissionWorkItem
					.getFormId());
			TargetManager tm = new TargetManager();
			for (Iterator iter = targetCategorys.iterator(); iter.hasNext();) {
				TargetsCategory tc = (TargetsCategory) iter.next();
				List targetGroup = new ArrayList();
				for (Iterator subIter = submissionWorks.iterator(); subIter
						.hasNext();) {
					SubmissionWorkItem swi = (SubmissionWorkItem) subIter
							.next();
					List subTargetData = new ArrayList();
					ps = conn.prepareStatement(sql.toString());
					ps.setString(1, swi.getFormId());
					ps.setString(2, tc.getId());
					ps.setString(3, swi.getStaffId());
					ps.setString(4, swi.getProcId());
					ps.setString(5, swi.getActId());

					rs = ps.executeQuery();
					while (rs.next()) {
						GlobalTargetsData gtd = new GlobalTargetsData();

						gtd.setId(rs.getString("id"));
						gtd.setActId(rs.getString("act_id"));
						gtd.setText(rs.getString("target_name"));
						gtd.setKernel_value(rs.getString("kernel_value"));
						gtd.setNum_ext01(rs.getBigDecimal("num_ext01"));
						gtd.setNum_ext02(rs.getBigDecimal("num_ext02"));
						gtd.setNum_ext03(rs.getBigDecimal("num_ext03"));
						gtd.setNum_ext04(rs.getBigDecimal("num_ext04"));
						gtd.setNum_ext05(rs.getBigDecimal("num_ext05"));
						gtd.setNum_ext06(rs.getBigDecimal("num_ext06"));
						gtd.setNum_ext07(rs.getBigDecimal("num_ext07"));
						gtd.setNum_ext08(rs.getBigDecimal("num_ext08"));
						gtd.setNum_ext09(rs.getBigDecimal("num_ext09"));
						gtd.setNum_ext10(rs.getBigDecimal("num_ext10"));
						gtd.setChar_ext01(rs.getString("char_ext01"));
						gtd.setChar_ext02(rs.getString("char_ext02"));
						gtd.setChar_ext03(rs.getString("char_ext03"));
						gtd.setChar_ext04(rs.getString("char_ext04"));
						gtd.setChar_ext05(rs.getString("char_ext05"));
						gtd.setDocumentId(rs.getString("document_id"));
						gtd.setProcId(rs.getString("proc_id"));
						gtd.setCategoryId(tc.getId());
						gtd.setTargetId(rs.getString("target_id"));
						gtd.setSubmiUser(rs.getString("submi_user"));
						Date subDate = rs.getDate("sub_date");
						gtd.setSubDate(Format.getInstance().dateToYMD(subDate));

						List targetDetails = tm.findTargetDetails(gtd.getId());
						gtd.setTargetDetails(targetDetails);

						subTargetData.add(gtd);
					}
					targetGroup.add(subTargetData);
				}
				tc.setTargets(targetGroup);
				templateData.add(tc);
				DBUtils.close(rs, ps);
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("getApproveActivityData throw error！");
		} finally {
			DBUtils.close(rs, ps, conn);
		}

		return templateData;

	}

	public List getTargetDataCollectByGroupHeaders(List groupHeaders)
			throws DaoException {

		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"select g.target_id,t.target_name,g.kernel_value,g.num_ext01,g.num_ext02,g.num_ext03, ")
				.append(
						"g.num_ext04,g.num_ext05,g.num_ext06,g.num_ext07,g.num_ext08,g.num_ext09,g.num_ext10,")
				.append(
						"g.char_ext01,g.char_ext02,g.char_ext03,g.char_ext04,g.char_ext05,g.document_id,g.submi_user,g.sub_date ")
				.append(
						"from dc_global_data g left join dc_target t on g.target_id=t.id ")
				.append("  where g.document_id=? and g.i_template = 1 ")
				.append(
						"  and g.targets_category_id=? and g.submi_user=? and g.proc_id=? and g.act_id=? ");
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List templateData = null;
		try {
			templateData = new ArrayList();
			conn = this.getConnection();
			List targetCategorys = new ArrayList();
			GroupHeader submissionWorkItem = (GroupHeader) groupHeaders.get(0);
			targetCategorys = this.getTargetCategorys(submissionWorkItem
					.getFormId());
			TargetManager tm = new TargetManager();
			for (Iterator iter = targetCategorys.iterator(); iter.hasNext();) {
				TargetsCategory tc = (TargetsCategory) iter.next();
				List targetGroup = new ArrayList();
				for (Iterator subIter = groupHeaders.iterator(); subIter
						.hasNext();) {
					GroupHeader swi = (GroupHeader) subIter.next();
					List subTargetData = new ArrayList();
					ps = conn.prepareStatement(sql.toString());
					ps.setString(1, swi.getFormId());
					ps.setString(2, tc.getId());
					ps.setString(3, swi.getStaffId());
					ps.setString(4, swi.getProcId());
					ps.setString(5, swi.getId());

					rs = ps.executeQuery();
					while (rs.next()) {
						GlobalTargetsData gtd = new GlobalTargetsData();

						gtd.setId(rs.getString("target_id"));
						gtd.setActId(rs.getString("act_id"));
						gtd.setText(rs.getString("target_name"));
						gtd.setKernel_value(rs.getString("kernel_value"));
						gtd.setNum_ext01(rs.getBigDecimal("num_ext01"));
						gtd.setNum_ext02(rs.getBigDecimal("num_ext02"));
						gtd.setNum_ext03(rs.getBigDecimal("num_ext03"));
						gtd.setNum_ext04(rs.getBigDecimal("num_ext04"));
						gtd.setNum_ext05(rs.getBigDecimal("num_ext05"));
						gtd.setNum_ext06(rs.getBigDecimal("num_ext06"));
						gtd.setNum_ext07(rs.getBigDecimal("num_ext07"));
						gtd.setNum_ext08(rs.getBigDecimal("num_ext08"));
						gtd.setNum_ext09(rs.getBigDecimal("num_ext09"));
						gtd.setNum_ext10(rs.getBigDecimal("num_ext10"));
						gtd.setChar_ext01(rs.getString("char_ext01"));
						gtd.setChar_ext02(rs.getString("char_ext02"));
						gtd.setChar_ext03(rs.getString("char_ext03"));
						gtd.setChar_ext04(rs.getString("char_ext04"));
						gtd.setChar_ext05(rs.getString("char_ext05"));
						gtd.setDocumentId(rs.getString("document_id"));
						gtd.setProcId(rs.getString("proc_id"));
						gtd.setCategoryId(tc.getId());
						gtd.setTargetId(rs.getString("target_id"));
						gtd.setSubmiUser(rs.getString("submi_user"));
						Date subDate = rs.getDate("sub_date");
						gtd.setSubDate(Format.getInstance().dateToYMD(subDate));

						List targetDetails = tm.findTargetDetails(gtd.getId());
						gtd.setTargetDetails(targetDetails);

						subTargetData.add(gtd);
					}
					targetGroup.add(subTargetData);
				}
				tc.setTargets(targetGroup);
				templateData.add(tc);
				DBUtils.close(rs, ps);
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("getApproveActivityData throw error！");
		} finally {
			DBUtils.close(rs, ps, conn);
		}

		return templateData;

	}

	public List getTargetDataCollectByGroupHeaders(List groupHeaders,
			String categoryId) throws DaoException {

		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"select g.target_id,t.target_name,g.kernel_value,g.num_ext01,g.num_ext02,g.num_ext03, ")
				.append(
						"g.num_ext04,g.num_ext05,g.num_ext06,g.num_ext07,g.num_ext08,g.num_ext09,g.num_ext10,")
				.append(
						"g.char_ext01,g.char_ext02,g.char_ext03,g.char_ext04,g.char_ext05,g.document_id,g.submi_user,g.sub_date ")
				.append(
						"from dc_global_data g left join dc_target t on g.target_id=t.id ")
				.append("  where g.document_id=? and g.i_template = 1 ")
				.append(
						"  and g.targets_category_id=? and g.submi_user=? and g.proc_id=? and g.act_id=? ");
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List templateData = null;
		try {
			templateData = new ArrayList();
			conn = this.getConnection();
			TargetManager tm = new TargetManager();
			for (Iterator subIter = groupHeaders.iterator(); subIter.hasNext();) {
				GroupHeader swi = (GroupHeader) subIter.next();
				List subTargetData = new ArrayList();
				ps = conn.prepareStatement(sql.toString());
				ps.setString(1, swi.getFormId());
				ps.setString(2, categoryId);
				ps.setString(3, swi.getStaffId());
				ps.setString(4, swi.getProcId());
				ps.setString(5, swi.getId());

				rs = ps.executeQuery();
				while (rs.next()) {
					GlobalTargetsData gtd = new GlobalTargetsData();

					gtd.setId(rs.getString("target_id"));
					gtd.setActId(rs.getString("act_id"));
					gtd.setText(rs.getString("target_name"));
					gtd.setKernel_value(rs.getString("kernel_value"));
					gtd.setNum_ext01(rs.getBigDecimal("num_ext01"));
					gtd.setNum_ext02(rs.getBigDecimal("num_ext02"));
					gtd.setNum_ext03(rs.getBigDecimal("num_ext03"));
					gtd.setNum_ext04(rs.getBigDecimal("num_ext04"));
					gtd.setNum_ext05(rs.getBigDecimal("num_ext05"));
					gtd.setNum_ext06(rs.getBigDecimal("num_ext06"));
					gtd.setNum_ext07(rs.getBigDecimal("num_ext07"));
					gtd.setNum_ext08(rs.getBigDecimal("num_ext08"));
					gtd.setNum_ext09(rs.getBigDecimal("num_ext09"));
					gtd.setNum_ext10(rs.getBigDecimal("num_ext10"));
					gtd.setChar_ext01(rs.getString("char_ext01"));
					gtd.setChar_ext02(rs.getString("char_ext02"));
					gtd.setChar_ext03(rs.getString("char_ext03"));
					gtd.setChar_ext04(rs.getString("char_ext04"));
					gtd.setChar_ext05(rs.getString("char_ext05"));
					gtd.setDocumentId(rs.getString("document_id"));
					gtd.setProcId(rs.getString("proc_id"));
					gtd.setCategoryId(categoryId);
					gtd.setTargetId(rs.getString("target_id"));
					gtd.setSubmiUser(rs.getString("submi_user"));
					Date subDate = rs.getDate("sub_date");
					gtd.setSubDate(Format.getInstance().dateToYMD(subDate));

					List targetDetails = tm.findTargetDetails(gtd.getId());
					gtd.setTargetDetails(targetDetails);

					subTargetData.add(gtd);
				}
				templateData.add(subTargetData);
			}
			DBUtils.close(rs, ps);
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("getApproveActivityData throw error！");
		} finally {
			DBUtils.close(rs, ps, conn);
		}

		return templateData;

	}

	/**
	 * 获取动态列头的信息
	 * 
	 * @param procId
	 * @param actId
	 * @return
	 * @throws Exception
	 */
	public List getGroupHeadByAct(String procId, String approveWorkId, int flag)
			throws Exception {
		ApproveWorkItemManager awm = new ApproveWorkItemManager();
		ApproveWorkItem awi = awm.getByKey(approveWorkId);
		List groupHeaders = new ArrayList();
		Connection conn = null;
		try {
			conn = this.getConnection();

			if (flag == Constants.DATACOLLECTVIEW_REQUEST_TYPE_SUBMIDATA) {// 请求的是当前上报的任务数据
				// 得到当前上报的步骤类型
				int preActType = awi.getPreActType();
				String preActId = awi.getPreActId();

				// 当前上报的是采集步骤
				if (preActType == Constants.COLLECTION_STEP_TYPE ) {
					ActivityManager activityManager = new ActivityManager();
					ActivityInstance activityInstance = activityManager
							.getActivityInfoByKey(preActId);
					GroupHeader topHeade = new GroupHeader();
					topHeade.setType(2);
					topHeade.setHeader(activityInstance.getActivityName());
					topHeade.setFormId(awi.getFormId());
					List headers = this.getSubWorkByAct(preActId, conn);
					topHeade.setChildren(headers);
					groupHeaders.add(topHeade);
				} else if (preActType == Constants.APPROVE_STEP_TYPE) {
					groupHeaders = this.getGroupHeadByApproveWork(procId,
							preActId, conn);
				}
			} else if (flag == Constants.DATACOLLECTVIEW_REQUEST_TYPE_COLLECT) {// 请求的是汇总所有上报数据

					groupHeaders = this.getGroupHeadByApproveWork(procId, awi
							.getActId(), conn);
				
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("getGroupHeadByAct throw error!");
		} finally {
			DBUtils.close(conn);
		}

		return groupHeaders;
	}

	private List getGroupHeadByApproveWork(String procId, String actId,
			Connection conn) throws Exception {

		StringBuffer sqlString = new StringBuffer();

		sqlString
				.append(
						"select id,proc_id,item_id,para_id,act_type,act_name,time_limit,appRole_Or_urgDate,fillType_Or_appType,")
				.append(
						"examRol_Or_colRol,description,exa_Or_col_type from dc_proc_instance where item_id=? and para_id=?");

		List activityList = null;
		try {
			activityList = new ArrayList();
			List children = new ArrayList();

			GroupHeader topHeade = this.getTopHeadeInfo(actId, conn);

			children = this.getSubWork4ApproveByAct(actId, conn);
			if(children == null || children.isEmpty()){
				children = this.getActivityInfo(conn, procId, actId, sqlString
						.toString());
			}
			
			topHeade.setChildren(children);
			activityList.add(topHeade);
		} catch (Exception e) {
			logger.error("", e);
			throw new Exception(Constants.SEARCH + "GroupHeadByApproveWork "
					+ Constants.FAIL);
		}
		return activityList;
	}

	private List getActivityInfo(Connection conn, String processId,
			String paraId, String sql) throws Exception {
		logger.info(Constants.INTOMETHOD + "getActivityInfo");
		PreparedStatement ps = null;
		ResultSet rs = null;
		List treeNodeArray = null;

		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, processId);
			ps.setString(2, paraId);
			rs = ps.executeQuery();
			treeNodeArray = new ArrayList();

			while (rs.next()) {
				int activityType = rs.getInt("act_type");
				String id = rs.getString("id");
				String text = rs.getString("act_name");

				switch (activityType) {
				case 0:
					GroupHeader groupHeader = new GroupHeader();
					groupHeader.setId(id);
					groupHeader.setType(activityType);
					groupHeader.setActId(id);
					groupHeader.setHeader(text);

					List children = new ArrayList();
					// 递归调用
					children = this.getActivityInfo(conn, processId, id, sql);
					if (!children.isEmpty()) {
						groupHeader.setChildren(children);
						groupHeader.setLeaf(false);
					} else {
						groupHeader.setLeaf(true);
					}
					treeNodeArray.add(groupHeader);
					break;
				case 1:
					GroupHeader ah = new GroupHeader();
					ah.setActId(id);
					ah.setHeader(text);
					ah.setType(activityType);

					List atChildren = new ArrayList();
					atChildren = this.getSubWork4ApproveByAct(id, conn);
					if(atChildren == null || atChildren.isEmpty()){
						// 递归调用
						atChildren = this.getActivityInfo(conn, processId, id, sql);
						
					}
					if (atChildren != null && !atChildren.isEmpty()) {
						ah.setChildren(atChildren);
						ah.setLeaf(false);
						treeNodeArray.add(ah);
					}
					
					break;
				case 2:
					GroupHeader ch = new GroupHeader();
					ch.setType(2);
					ch.setHeader(text);

					List ctChildren = new ArrayList();
					ctChildren = this.getSubWorkByAct(id, conn);
					if (ctChildren != null && !ctChildren.isEmpty()) {
						ch.setChildren(ctChildren);
						ch.setLeaf(false);
						treeNodeArray.add(ch);
					}
					break;
				}

			}

			logger.debug(Constants.SEARCH + "getActivityInfo tree"
					+ Constants.SUCCESS);

		} catch (Exception e) {
			logger.error("getActivityInfo() of throws : ", e);
			throw new Exception(Constants.SEARCH + "getActivityInfo tree"
					+ Constants.FAIL);
		} finally {
			DBUtils.close(rs, ps);
		}
		return treeNodeArray;
	}

	private List getSubWorkByAct(String actId, Connection conn)
			throws DaoException {

		List subWorks = null;
		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"select id,proc_id,act_id,staff_id,form_id from dc_submission_work where act_id=?")
				.append(
						" and sw_state<>0 and sw_state<>1 and sw_state<>2 and sw_state<>3 and sw_state<>6")
				.append(" and sw_state<>7 and sw_state<>8 and sw_state<>9 ");

		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR, actId));
		query.setPresentConnection(conn);
		query.setUseOnlyConnectionFlag(true);

		try {
			query.access();
			if (query.resultSetAvailable()) {
				subWorks = new ArrayList();
				// 获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());

				if (rs.rowCount > 0) {
					for (int i = 0; i < rs.rowCount; i++) {
						GroupHeader ghd = new GroupHeader();
						ghd.setId(rs.getFieldValueAsString("id"));
						ghd.setActId(actId);
						ghd.setProcId(rs.getFieldValueAsString("proc_id"));
						String statff = rs.getFieldValueAsString("staff_id");
						ghd.setStaffId(statff);
						String userName = this.getUserName(statff, conn);
						ghd.setHeader(userName);
						ghd.setFormId(rs.getFieldValueAsString("form_id"));
						ghd.setLeaf(true);

						subWorks.add(ghd);

						rs.next();
					}

				}

				rs.clearAll();
			}

		} catch (Exception e) {
			logger.error("查找环节编码为：" + actId + "的所有采集任务失败！", e);
			throw new DaoException("查找环节编码为：" + actId + "的所有采集任务失败！");
		}
		return subWorks;

	}

	private List getSubWork4ApproveByAct(String actId, Connection conn)
			throws DaoException {
		List subWorks = null;
		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"select id,proc_id,act_id,staff_id,form_id from dc_submission_work where act_id=?")
				.append(
						" and sw_type <> 8 ");

		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR, actId));
		query.setPresentConnection(conn);
		query.setUseOnlyConnectionFlag(true);

		try {
			query.access();
			if (query.resultSetAvailable()) {
				subWorks = new ArrayList();
				// 获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());

				if (rs.rowCount > 0) {
					GroupHeader ghd = new GroupHeader();
					ghd.setId(rs.getFieldValueAsString("id"));
					ghd.setActId(actId);
					ghd.setProcId(rs.getFieldValueAsString("proc_id"));
					String statff = rs.getFieldValueAsString("staff_id");
					ghd.setStaffId(statff);
					String userName = this.getUserName(statff, conn);
					ghd.setHeader(userName);
					ghd.setFormId(rs.getFieldValueAsString("form_id"));
					ghd.setLeaf(true);

					subWorks.add(ghd);
				}

				rs.clearAll();
			}

		} catch (Exception e) {
			logger.error("查找环节编码为：" + actId + "的所有采集任务失败！", e);
			throw new DaoException("查找环节编码为：" + actId + "的所有采集任务失败！");
		}
		return subWorks;

	}

	private String getUserName(String userCode, Connection conn)
			throws DaoException {
		String userName = "";
		StringBuffer sql = new StringBuffer();
		sql.append("select f.user_name from frameuser f where f.user_code = ?");

		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR, userCode));
		query.setPresentConnection(conn);
		query.setUseOnlyConnectionFlag(true);

		try {
			query.access();
			if (query.resultSetAvailable()) {
				// 获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				if (rs.rowCount > 0) {
					userName = rs.getFieldValueAsString("user_name");

				}
				rs.clearAll();
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("getUserName throw error！");
		}
		return userName;
	}

	private GroupHeader getTopHeadeInfo(String id, Connection conn)
			throws DaoException {
		StringBuffer sql = new StringBuffer();
		sql.append("select act_name from dc_proc_instance  where id = ?");

		GroupHeader topHeade = new GroupHeader();

		QueryOperator query = new QueryOperator();
		query.setSql(sql.toString());
		query.addParameter(new SqlParameter(SqlType.VARCHAR, id));
		query.setPresentConnection(conn);
		query.setUseOnlyConnectionFlag(true);

		try {
			query.access();
			if (query.resultSetAvailable()) {
				// 获取并处理记录集
				RsDataSet rs = new RsDataSet(query.getSqlResultSet());
				if (rs.rowCount > 0) {
					String actName = rs.getFieldValueAsString("act_name");
					topHeade.setHeader(actName);
					topHeade.setType(1);
				}
				rs.clearAll();
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("getTopHeadeInfo throw error！");
		}
		return topHeade;
	}

	public List getTargetCategorys1(String formId) {
		List targetCategorys = new ArrayList();
		StringBuffer sql = new StringBuffer();
		sql
				.append(
						"select distinct targets_category_id,c.category_name from dc_global_data g ")
				.append(
						" left join DC_item_CATEGORY c on g.targets_category_id = c.id ")
				.append("where document_id=? and i_template = 1");
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		try {
			conn = this.getConnection();
			ps = conn.prepareStatement(sql.toString());
			ps.setString(1, formId);
			rs = ps.executeQuery();
			while (rs.next()) {
				TargetsCategory tc = new TargetsCategory();
				tc.setId(rs.getString("targets_category_id"));
				tc.setCategoryName(rs.getString("category_name"));
				targetCategorys.add(tc);
			}
		} catch (Exception e) {
			logger.error("", e);
			return targetCategorys;
		} finally {
			DBUtils.close(rs, ps, conn);
		}

		return targetCategorys;
	}

	public List getTemplateData(String documentId, String procId,List submissionWorkItems)
			throws DaoException {

		StringBuffer sql = new StringBuffer();
		sql
				.append("select g.*,t.target_name ")
				.append(
						"from dc_global_data g left join dc_target t on g.target_id=t.id ")
				.append(
						" where g.document_id=? and proc_id = ? and g.i_template = 1 and g.targets_category_id=? and act_id=? ")
				.append(" order by to_number(act_id),to_number(target_id) asc");

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List templateData = null;
		try {
			templateData = new ArrayList();
			conn = this.getConnection();
			List targetCategorys = new ArrayList();
			targetCategorys = this.getTargetCategorys1(documentId);
			TargetManager tm = new TargetManager();
			for (Iterator iter = targetCategorys.iterator(); iter.hasNext();) {
				TargetsCategory tc = (TargetsCategory) iter.next();
				List targetGroup = new ArrayList();
				ps = conn.prepareStatement(sql.toString());
				for(Iterator iterator = submissionWorkItems.iterator();iterator.hasNext();){
					SubmissionWorkItem swi = (SubmissionWorkItem)iterator.next();

					ps.setString(1, documentId);
					ps.setString(2, procId);
					ps.setString(3, tc.getId());
					ps.setString(4, swi.getId());
					rs = ps.executeQuery();
					while (rs.next()) {
						GlobalTargetsDataVO gtd = new GlobalTargetsDataVO();
						gtd.setId(rs.getString("target_id"));
						gtd.setActId(rs.getString("act_id"));
						gtd.setText(rs.getString("target_name"));
						gtd.setKernel_value(rs.getString("kernel_value"));
						gtd.setNum_ext01(rs.getFloat("num_ext01"));
						gtd.setNum_ext02(rs.getFloat("num_ext02"));
						gtd.setNum_ext03(rs.getFloat("num_ext03"));
						gtd.setNum_ext04(rs.getFloat("num_ext04"));
						gtd.setNum_ext05(rs.getFloat("num_ext05"));
						gtd.setNum_ext06(rs.getFloat("num_ext06"));
						gtd.setNum_ext07(rs.getFloat("num_ext07"));
						gtd.setNum_ext08(rs.getFloat("num_ext08"));
						gtd.setNum_ext09(rs.getFloat("num_ext09"));
						gtd.setNum_ext10(rs.getFloat("num_ext10"));
						gtd.setChar_ext01(rs.getString("char_ext01"));
						gtd.setChar_ext02(rs.getString("char_ext02"));
						gtd.setChar_ext03(rs.getString("char_ext03"));
						gtd.setChar_ext04(rs.getString("char_ext04"));
						gtd.setChar_ext05(rs.getString("char_ext05"));
						gtd.setFormId(rs.getString("document_id"));
						gtd.setProcId(rs.getString("proc_id"));
						gtd.setCategoryId(tc.getId());
						gtd.setTargetId(rs.getString("target_id"));
						gtd.setGlobalDataId(rs.getString("id"));
						gtd.setSubmiUser(rs.getString("submi_user"));
						Date subDate = rs.getDate("sub_date");
						gtd.setSubDate(Format.getInstance().dateToYMD(subDate));

						List targetDetails = tm.findTargetDetails(gtd.getId());
						// 倒排的，需要翻转
						List targetDetailsAsc = new ArrayList(targetDetails.size());
						for (int i = targetDetails.size() - 1; i >= 0; i--) {
							targetDetailsAsc.add(targetDetails.get(i));
						}
						gtd.setTargetDetails(targetDetailsAsc);

						targetGroup.add(gtd);
					}
				}
				
				tc.setTargets(targetGroup);
				templateData.add(tc);
				DBUtils.close(rs, ps);
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("取得公文id为：" + documentId + " 的指标报送模板初始化数据出错！");
		} finally {
			DBUtils.close(rs, ps, conn);
		}

		return templateData;
	}

	public List getTemplateDataByActId(String documentId, String procId,
			String actId, String approveId) throws DaoException {

		StringBuffer sql = new StringBuffer();
		sql
				.append("select g.*,t.target_name ")
				.append(
						"from dc_global_data g left join dc_target t on g.target_id=t.id ")
				.append(
						" where g.document_id=? and proc_id = ? and g.i_template = 1 and g.targets_category_id=? "
								+ "and act_id in (select s.id from dc_submission_work s, dc_approve_work a where s.act_id = a.pre_act_id and a.id=?)"
								+ "order by to_number(act_id),to_number(target_id) asc");

		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List templateData = null;
		try {
			templateData = new ArrayList();
			conn = this.getConnection();
			List targetCategorys = new ArrayList();
			targetCategorys = this.getTargetCategorys1(documentId);
			TargetManager tm = new TargetManager();
			for (Iterator iter = targetCategorys.iterator(); iter.hasNext();) {
				TargetsCategory tc = (TargetsCategory) iter.next();
				List targetGroup = new ArrayList();
				ps = conn.prepareStatement(sql.toString());
				ps.setString(1, documentId);
				ps.setString(2, procId);
				ps.setString(3, tc.getId());
				ps.setString(4, approveId);
				rs = ps.executeQuery();
				while (rs.next()) {
					GlobalTargetsDataVO gtd = new GlobalTargetsDataVO();
					gtd.setId(rs.getString("target_id"));
					gtd.setActId(rs.getString("act_id"));
					gtd.setText(rs.getString("target_name"));
					gtd.setKernel_value(rs.getString("kernel_value"));
					gtd.setNum_ext01(rs.getFloat("num_ext01"));
					gtd.setNum_ext02(rs.getFloat("num_ext02"));
					gtd.setNum_ext03(rs.getFloat("num_ext03"));
					gtd.setNum_ext04(rs.getFloat("num_ext04"));
					gtd.setNum_ext05(rs.getFloat("num_ext05"));
					gtd.setNum_ext06(rs.getFloat("num_ext06"));
					gtd.setNum_ext07(rs.getFloat("num_ext07"));
					gtd.setNum_ext08(rs.getFloat("num_ext08"));
					gtd.setNum_ext09(rs.getFloat("num_ext09"));
					gtd.setNum_ext10(rs.getFloat("num_ext10"));
					gtd.setChar_ext01(rs.getString("char_ext01"));
					gtd.setChar_ext02(rs.getString("char_ext02"));
					gtd.setChar_ext03(rs.getString("char_ext03"));
					gtd.setChar_ext04(rs.getString("char_ext04"));
					gtd.setChar_ext05(rs.getString("char_ext05"));
					gtd.setFormId(rs.getString("document_id"));
					gtd.setProcId(rs.getString("proc_id"));
					gtd.setCategoryId(tc.getId());
					gtd.setTargetId(rs.getString("target_id"));
					gtd.setGlobalDataId(rs.getString("id"));
					gtd.setSubmiUser(rs.getString("submi_user"));
					Date subDate = rs.getDate("sub_date");
					gtd.setSubDate(Format.getInstance().dateToYMD(subDate));

					List targetDetails = tm.findTargetDetails(gtd.getId());
					// 倒排的，需要翻转
					List targetDetailsAsc = new ArrayList(targetDetails.size());
					for (int i = targetDetails.size() - 1; i >= 0; i--) {
						targetDetailsAsc.add(targetDetails.get(i));
					}

					gtd.setTargetDetails(targetDetailsAsc);

					targetGroup.add(gtd);
				}
				tc.setTargets(targetGroup);
				templateData.add(tc);
				DBUtils.close(rs, ps);
			}
		} catch (Exception e) {
			logger.error("", e);
			throw new DaoException("取得公文id为：" + documentId + " 的指标报送模板初始化数据出错！");
		} finally {
			DBUtils.close(rs, ps, conn);
		}

		return templateData;
	}
	
	private void delete(String actId,String procId,int currentOrParrent,int actType) throws DaoException {
		StringBuffer sql = new StringBuffer();
		sql.append(" delete from dc_global_data where act_id = ? ");
		
		SubmissionWorkItemManager swm = new SubmissionWorkItemManager();
		List submissionWorkItems = new ArrayList();

		try{
			submissionWorkItems = swm.getAllSubWorksByAct(procId, actId, currentOrParrent, actType);
			
			UpdateOperator update = new UpdateOperator(); 
			update.setSql(sql.toString());
			
			for(Iterator iter = submissionWorkItems.iterator();iter.hasNext();) {
				SubmissionWorkItem swi = (SubmissionWorkItem)iter.next();
				SqlParameterSet spSet = new SqlParameterSet();
				spSet.addParameter(new SqlParameter(SqlType.VARCHAR,swi.getId()));

				update.addBatchParameter(spSet);
			}
			update.access();	
		}catch(Exception e){
			logger.error("", e);
			throw new DaoException("delete throw error!");
		}
	}
	
	public boolean save(List globalTargetsDataList, String updateUser,String procId,String actId,int currentOrParrent,int actType)
	 throws DaoException {
		if (logger.isDebugEnabled()) {
			logger.debug(Constants.INTOMETHOD + "save");
		}
	    Connection conn = null;
		
		StringBuffer sqlSave = new StringBuffer();
		sqlSave
				.append(
						"insert into dc_global_data (document_id,targets_category_id,target_id,kernel_value,num_ext01,num_ext02,num_ext03,num_ext04,")
				.append(
						"num_ext05,num_ext06,num_ext07,num_ext08,num_ext09,num_ext10,char_ext01,char_ext02,char_ext03,char_ext04,char_ext05,i_template,")
				.append(
						"submi_user,proc_id,act_id,id,sub_date,update_user,up_date) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,sysdate)");
		
		UpdateOperator update = new UpdateOperator();
		
		try{
			conn = this.getConnection();
		
			//保存新数据之前先删除原有的数据
			this.delete(actId, procId, currentOrParrent,actType);
			
			update.setUseOnlyConnectionFlag(true);
			update.setPresentConnection(conn);
			update.setSql( sqlSave.toString());

			for (Iterator iter = globalTargetsDataList.iterator(); iter.hasNext();) {
				GlobalTargetsDataListVO gtdj = (GlobalTargetsDataListVO) iter
						.next();
				for(Iterator iter2 = gtdj.getTargets().iterator();iter2.hasNext();){
					GlobalTargetsDataVO gtd = (GlobalTargetsDataVO)iter2.next();
					SqlParameterSet spSet = new SqlParameterSet();
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getFormId()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getCategoryId()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getTargetId()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getKernel_value()));
					spSet.addParameter(new SqlParameter(SqlType.FLOAT,new Float(gtd.getNum_ext01())));
					spSet.addParameter(new SqlParameter(SqlType.FLOAT,new Float(gtd.getNum_ext02())));
					spSet.addParameter(new SqlParameter(SqlType.FLOAT,new Float(gtd.getNum_ext03())));
					spSet.addParameter(new SqlParameter(SqlType.FLOAT,new Float(gtd.getNum_ext04())));
					spSet.addParameter(new SqlParameter(SqlType.FLOAT,new Float(gtd.getNum_ext05())));
					spSet.addParameter(new SqlParameter(SqlType.FLOAT,new Float(gtd.getNum_ext06())));
					spSet.addParameter(new SqlParameter(SqlType.FLOAT,new Float(gtd.getNum_ext07())));
					spSet.addParameter(new SqlParameter(SqlType.FLOAT,new Float(gtd.getNum_ext08())));
					spSet.addParameter(new SqlParameter(SqlType.FLOAT,new Float(gtd.getNum_ext09())));
					spSet.addParameter(new SqlParameter(SqlType.FLOAT,new Float(gtd.getNum_ext10())));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getChar_ext01()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getChar_ext02()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getChar_ext03()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getChar_ext04()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getChar_ext05()));
					spSet.addParameter(new SqlParameter(SqlType.INTEGER,new Integer(1)));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getSubmiUser()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getProcId()));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,gtd.getActId()));
					String id = String.valueOf(this.getSeq("dc_global_data"));
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,id));
					String subDateStr = gtd.getSubDate();
					if(StringUtils.isEmpty(subDateStr)){
						spSet.addParameter(new SqlParameter(SqlType.DATE,null));
					}else{
						Date subDate = Format.getInstance().stringToDate(gtd.getSubDate(),"yyyy-MM-dd");
						spSet.addParameter(new SqlParameter(SqlType.DATE,new java.sql.Date(subDate.getTime()) ));
					}
					spSet.addParameter(new SqlParameter(SqlType.VARCHAR,updateUser));
					
					update.addBatchParameter(spSet);
				}
			}
			update.access();
		}catch(Exception e){
			logger.error("保存审批人修改的指标报送数据出错！", e);
			throw new DaoException("保存审批人修改的指标报送数据出错！");
		}finally{
			DBUtils.close(conn);
		}
		
		int[] effectCount = update.getBatchEffectCounts();
		if(effectCount != null && effectCount.length>0) {
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + "保存审批人修改的指标报送数据"
						+ Constants.SUCCESS);
			}
			return true;
		}else{
			if (logger.isDebugEnabled()) {
				logger.debug("save:" + "保存审批人修改的指标报送数据"
						+ Constants.FAIL);
			}
			return false;
		}
	}

}
